package cn.com.dashihui.web.service;

import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.jfinal.aop.Before;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.tx.Tx;

import cn.com.dashihui.kit.ExcelExportUtil;
import cn.com.dashihui.kit.ExcelExportUtil.Pair;
import cn.com.dashihui.web.dao.Category;
import cn.com.dashihui.web.dao.DetailsImg;
import cn.com.dashihui.web.dao.GoodsBaseImages;
import cn.com.dashihui.web.dao.GoodsSale;
import cn.com.dashihui.web.dao.GoodsSaleItem;
import cn.com.dashihui.web.dao.GoodsStore;
import cn.com.dashihui.web.dao.Seller;


public class GoodsStoreService {
	/**
	 * 分页查找分公司产品
	 * @param pageNum
	 * @param pageSize
	 * @param storeid 分公司ID
	 * @param s 商品在售状态，1：在售，2：已下架，3：待审核
	 * @param c1,c2,c3,c4 四级分类
	 * @param keyword 搜索关键字
	 */
	public Page<Record> findByPage(int pageNum, int pageSize,int goodId,String sGoodName,String storeid, String sBeginDate,String sEndDate, String xBeginDate, String xEndDate, int state,String sellerId){
		StringBuffer sBuffer = new StringBuffer("FROM t_bus_goods AS A "
				+ "INNER JOIN t_bus_goods_store_rel F ON A.good_store=F.goodstore "
				+ "INNER JOIN t_dict_store AS B ON F.storeid = B.storeid "
				+ "INNER JOIN t_bus_seller AS C ON B.sellerId = C.sellerId " 
				+ "LEFT JOIN (select L.goodsid ,sum(L.count) AS countSell from t_bus_order_list L left join t_bus_order O on O.orderNum = L.orderNum where O.orderState NOT in(1,5,7,9) group by L.goodsid) D ON D.goodsid = A.id "
				+ "LEFT JOIN (select J.goodsid ,sum(J.price) AS countPrice from t_bus_order_list J left join t_bus_order K on J.orderNum = K.orderNum  where K.orderState NOT in(1,5,7,9) group by J.goodsid) I ON I.goodsid = A.id "
				+ "LEFT JOIN ( SELECT COUNT(id) as countCon,goodsid FROM t_bus_user_collection WHERE isCancel = 0 group by goodsid) E ON E.goodsid = A.id "
				+ "left join t_bus_goods_toker T on T.goodsId=A.id "
				+ " left join t_bus_goods_look H on H.goodsid=A.id where 1=1 ");
		/*StringBuffer sBuffer = new StringBuffer("FROM t_bus_goods AS A "
				+ "INNER JOIN t_bus_goods_store_rel F ON A.good_store=F.goodstore "
				+ "INNER JOIN t_dict_store AS B ON F.storeid = B.storeid "
				+ "INNER JOIN t_bus_seller AS C ON B.sellerId = C.sellerId " 
				+ "LEFT JOIN (select L.goodsid ,sum(L.count) AS countSell from t_bus_order_list L left join t_bus_order O on O.orderNum = L.orderNum where O.orderState NOT in(1,5,7,9) group by L.goodsid) D ON D.goodsid = A.id "
				+ "LEFT JOIN (select J.goodsid ,sum(J.price) AS countPrice from t_bus_order_list J left join t_bus_order K on J.orderNum = K.orderNum  where K.orderState !=1 and K.orderState !=5 group by J.goodsid) I ON I.goodsid = A.id "
				+ "LEFT JOIN ( SELECT COUNT(id) as countCon,goodsid FROM t_bus_user_collection WHERE isCancel = 0 group by goodsid) E ON E.goodsid = A.id "
				+ "left join t_bus_goods_toker T on T.goodsId=A.id "
				+ " left join t_bus_goods_look H on H.goodsid=A.id where 1=1 ");*/
		List<Object> params = new ArrayList<Object>();
		sBuffer.append(" AND C.sellerId=? ");
		params.add(sellerId);
		if(goodId!=0){
			sBuffer.append(" AND A.id=?");
			params.add(goodId);
		}
		 if(!StrKit.isBlank(storeid)){
				sBuffer.append(" AND B.storeid in ('"+storeid+"')");
		 }
		 if(StrKit.notBlank(sGoodName)){
				sBuffer.append(" AND A.name like ?");
				params.add("%"+sGoodName+"%");
			}
	 /*  if(!StrKit.isBlank(sellerId)){
		
		}*/
		if(!StrKit.isBlank(sBeginDate)&&!StrKit.isBlank(sEndDate)){
			sBuffer.append("AND A.pstate=1 and A.state=2 AND A.uptime BETWEEN ? AND ?");
			params.add(sBeginDate);
			params.add(sEndDate);
		}else if(!StrKit.isBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
			sBuffer.append("AND A.pstate=1 and A.state=2 AND DATE_FORMAT(A.uptime,'%Y-%m-%d')>=?");
			params.add(sBeginDate);
		}else if(StrKit.isBlank(sBeginDate)&&!StrKit.isBlank(sEndDate)){
			sBuffer.append("AND A.pstate=1 and A.state=2 AND DATE_FORMAT(A.uptime,'%Y-%m-%d')<=?");
			params.add(sEndDate);
		}
		if(!StrKit.isBlank(xBeginDate)&&!StrKit.isBlank(xEndDate)){
			sBuffer.append("AND A.pstate=1 and A.state=3 AND A.downtime BETWEEN ? AND ?");
			params.add(xBeginDate);
			params.add(xEndDate);
		}else if(!StrKit.isBlank(xBeginDate)&&StrKit.isBlank(xEndDate)){
			sBuffer.append("AND A.pstate=1 and A.state=3 AND DATE_FORMAT(A.downtime,'%Y-%m-%d')>=?");
			params.add(xBeginDate);
		}else if(StrKit.isBlank(xBeginDate)&&!StrKit.isBlank(xEndDate)){
			sBuffer.append("AND A.pstate=1 and A.state=3 AND DATE_FORMAT(A.downtime,'%Y-%m-%d')<=?");
			params.add(xEndDate);
		}
		switch (state) {
		case 1:
			sBuffer.append(" AND A.state=1");
			break;
		case 2:
			sBuffer.append(" AND A.state=2");
			break;
		case 3:
			sBuffer.append(" AND A.state=3");
			break;
		case 4:
			sBuffer.append(" AND A.state=4");
			break;
		}
		sBuffer.append(" AND A.groupbuying = 1 ");
		/**/
		// GROUP BY A.id ORDER BY A.createDate DESC
		sBuffer.append(" group by A.id");
		sBuffer.append(" ORDER BY  A.uptime DESC");
		return Db.paginate(pageNum, pageSize, "SELECT  A.*,B.title,C.sellerName,C.sellerId,ifnull(D.countSell,0 ) as countSell,ifnull(I.countPrice,0 ) as countPrice,ifnull(E.countCon,0) as countCon,ifnull(H.conNum,0) as conNum,T.state as tState  ", sBuffer.toString(), params.toArray());
	}
	/**
	 * 分页查找团购产品
	 * @param pageNum
	 * @param pageSize
	 * @param storeid 分公司ID
	 * @param s 商品在售状态，1：在售，2：已下架，3：待审核
	 * @param c1,c2,c3,c4 四级分类
	 * @param keyword 搜索关键字
	 */
	
	public Page<Record> findByPagegb(int pageNum, int pageSize,int goodId,String sGoodName,String storeid, String sBeginDate,String sEndDate, String xBeginDate, String xEndDate, int state,String sellerId){
		StringBuffer sBuffer = new StringBuffer("FROM t_bus_goods AS A "
				+ "INNER JOIN t_bus_goods_store_rel F ON A.good_store=F.goodstore "
				+ "INNER JOIN t_dict_store AS B ON F.storeid = B.storeid "
				+ "INNER JOIN t_bus_seller AS C ON B.sellerId = C.sellerId " 
				+ "LEFT JOIN (select goodsid ,count(id) AS countSell from t_bus_order_gb where orderState NOT in(1,5,7,9) group by goodsid) D ON D.goodsid = A.id "
				+ "LEFT JOIN (select goodsid ,sum(amount) AS countPrice from t_bus_order_gb where orderState NOT in(1,5,7,9) group by goodsid) I ON I.goodsid = A.id "
				+ "LEFT JOIN ( SELECT COUNT(id) as countCon,goodsid FROM t_bus_user_collection WHERE isCancel = 0 group by goodsid) E ON E.goodsid = A.id "
				+ "left join t_bus_goods_toker T on T.goodsId=A.id "
				+ " left join t_bus_goods_look H on H.goodsid=A.id where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		sBuffer.append(" AND C.sellerId=? ");
		params.add(sellerId);
		if(goodId!=0){
			sBuffer.append(" AND A.id=?");
			params.add(goodId);
		}
		 if(!StrKit.isBlank(storeid)){
				sBuffer.append(" AND B.storeid in ('"+storeid+"')");
		 }
		 if(StrKit.notBlank(sGoodName)){
				sBuffer.append(" AND A.name like ?");
				params.add("%"+sGoodName+"%");
			}
	 /*  if(!StrKit.isBlank(sellerId)){
		
		}*/
		if(!StrKit.isBlank(sBeginDate)&&!StrKit.isBlank(sEndDate)){
			sBuffer.append("AND A.pstate=1 and A.state=2 AND A.uptime BETWEEN ? AND ?");
			params.add(sBeginDate);
			params.add(sEndDate);
		}else if(!StrKit.isBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
			sBuffer.append("AND A.pstate=1 and A.state=2 AND DATE_FORMAT(A.uptime,'%Y-%m-%d')>=?");
			params.add(sBeginDate);
		}else if(StrKit.isBlank(sBeginDate)&&!StrKit.isBlank(sEndDate)){
			sBuffer.append("AND A.pstate=1 and A.state=2 AND DATE_FORMAT(A.uptime,'%Y-%m-%d')<=?");
			params.add(sEndDate);
		}
		if(!StrKit.isBlank(xBeginDate)&&!StrKit.isBlank(xEndDate)){
			sBuffer.append("AND A.pstate=1 and A.state=3 AND A.downtime BETWEEN ? AND ?");
			params.add(xBeginDate);
			params.add(xEndDate);
		}else if(!StrKit.isBlank(xBeginDate)&&StrKit.isBlank(xEndDate)){
			sBuffer.append("AND A.pstate=1 and A.state=3 AND DATE_FORMAT(A.downtime,'%Y-%m-%d')>=?");
			params.add(xBeginDate);
		}else if(StrKit.isBlank(xBeginDate)&&!StrKit.isBlank(xEndDate)){
			sBuffer.append("AND A.pstate=1 and A.state=3 AND DATE_FORMAT(A.downtime,'%Y-%m-%d')<=?");
			params.add(xEndDate);
		}
		switch (state) {
		case 1:
			sBuffer.append(" AND A.state=1");
			break;
		case 2:
			sBuffer.append(" AND A.state=2");
			break;
		case 3:
			sBuffer.append(" AND A.state=3");
			break;
		case 4:
			sBuffer.append(" AND A.state=4");
			break;
		}
		
		sBuffer.append(" AND A.groupbuying=2");
		sBuffer.append(" ORDER BY A.uptime DESC");
		return Db.paginate(pageNum, pageSize, "SELECT  A.*,B.title,C.sellerName,C.sellerId,ifnull(D.countSell,0 ) as countSell,ifnull(I.countPrice,0 ) as countPrice,ifnull(E.countCon,0) as countCon,ifnull(H.conNum,0) as conNum,T.state as tState ", sBuffer.toString(), params.toArray());
	}
	/**
	 * 排序
	 * 
	 * @param pageNum
	 * @param pageSize
	 * @param index
	 * @param goodId
	 * @param storeid
	 * @param sBeginDate
	 * @param sEndDate
	 * @param xBeginDate
	 * @param xEndDate
	 * @param state
	 * @param sellerId
	 * @return
	 */
	public Page<Record> findByPageSort(String index, int pageNum, int pageSize, String storeid, int goodId,String sGoodName, String storeid2,
			String sBeginDate, String sEndDate, String xBeginDate, String xEndDate, int state, String sSellerId){
		StringBuffer sBuffer = new StringBuffer("FROM t_bus_goods AS A "
				+ "INNER JOIN t_bus_goods_store_rel F ON A.good_store=F.goodstore "
				+ "INNER JOIN t_dict_store AS B ON F.storeid = B.storeid "
				+ "INNER JOIN t_bus_seller AS C ON B.sellerId = C.sellerId " 
				+ "LEFT JOIN (select L.goodsid ,sum(L.count) AS countSell from t_bus_order_list L left join t_bus_order O on O.orderNum = L.orderNum where O.orderState NOT in(1,5,7,9) group by L.goodsid) D ON D.goodsid = A.id "
				+ "LEFT JOIN (select J.goodsid ,sum(J.price) AS countPrice from t_bus_order_list J left join t_bus_order K on J.orderNum = K.orderNum  where K.orderState NOT in(1,5,7,9) group by J.goodsid) I ON I.goodsid = A.id "
				+ "LEFT JOIN ( SELECT COUNT(id) as countCon,goodsid FROM t_bus_user_collection WHERE isCancel = 0 group by goodsid) E ON E.goodsid = A.id "
				+ "left join t_bus_goods_toker T on T.goodsId=A.id "
				+ " left join t_bus_goods_look H on H.goodsid=A.id where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		sBuffer.append(" AND C.sellerId=? ");
		params.add(sSellerId);
		if(goodId!=0){
			sBuffer.append(" AND A.id=?");
			params.add(goodId);
		}
		 if(!StrKit.isBlank(storeid)){
				sBuffer.append(" AND B.storeid in ('"+storeid+"')");
		 }
		 if(StrKit.notBlank(sGoodName)){
				sBuffer.append(" AND A.name like ?");
				params.add("%"+sGoodName+"%");
			}
		if(!StrKit.isBlank(sBeginDate)&&!StrKit.isBlank(sEndDate)){
			sBuffer.append("AND A.pstate=1 and A.state=2 AND A.uptime BETWEEN ? AND ?");
			params.add(sBeginDate);
			params.add(sEndDate);
		}else if(!StrKit.isBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
			sBuffer.append("AND A.pstate=1 and A.state=2 AND DATE_FORMAT(A.uptime,'%Y-%m-%d')>=?");
			params.add(sBeginDate);
		}else if(StrKit.isBlank(sBeginDate)&&!StrKit.isBlank(sEndDate)){
			sBuffer.append("AND A.pstate=1 and A.state=2 AND DATE_FORMAT(A.uptime,'%Y-%m-%d')<=?");
			params.add(sEndDate);
		}
		if(!StrKit.isBlank(xBeginDate)&&!StrKit.isBlank(xEndDate)){
			sBuffer.append("AND A.pstate=1 and A.state=3 AND A.downtime BETWEEN ? AND ?");
			params.add(xBeginDate);
			params.add(xEndDate);
		}else if(!StrKit.isBlank(xBeginDate)&&StrKit.isBlank(xEndDate)){
			sBuffer.append("AND A.pstate=1 and A.state=3 AND DATE_FORMAT(A.downtime,'%Y-%m-%d')>=?");
			params.add(xBeginDate);
		}else if(StrKit.isBlank(xBeginDate)&&!StrKit.isBlank(xEndDate)){
			sBuffer.append("AND A.pstate=1 and A.state=3 AND DATE_FORMAT(A.downtime,'%Y-%m-%d')<=?");
			params.add(xEndDate);
		}
		switch (state) {
		case 1:
			sBuffer.append(" AND A.state=1");
			break;
		case 2:
			sBuffer.append(" AND A.state=2");
			break;
		case 3:
			sBuffer.append(" AND A.state=3");
			break;
		case 4:
			sBuffer.append(" AND A.state=4");
			break;
		}
		sBuffer.append(" AND A.groupbuying = 1 ");
		//订单量、收藏量、浏览量排序功能，可按大到小排 从小到大
		if(!StrKit.isBlank(index)){
			switch (index) {
				case "1":
					sBuffer.append(" GROUP BY A.id ORDER BY D.countSell DESC");
					break;
				case "2":
					sBuffer.append(" GROUP BY A.id ORDER BY D.countSell ASC");
					break;
				case "3":
					sBuffer.append(" GROUP BY A.id ORDER BY E.countCon DESC");
					break;
				case "4":
					sBuffer.append(" GROUP BY A.id ORDER BY E.countCon ASC");
					break;
				case "5":
					sBuffer.append(" GROUP BY A.id ORDER BY H.conNum DESC");
					break;
				case "6":
					sBuffer.append(" GROUP BY A.id ORDER BY H.conNum ASC");
					break;
				case "7":
					sBuffer.append(" GROUP BY A.id ORDER BY I.countPrice DESC");
					break;
				case "8":
					sBuffer.append(" GROUP BY A.id ORDER BY I.countPrice ASC");
					break;
			}
		}
		return Db.paginate(pageNum, pageSize, "SELECT A.*,GROUP_CONCAT(B.title) as title,C.sellerName,C.sellerId,ifnull(D.countSell,0 ) as countSell,ifnull(I.countPrice,0 ) as countPrice,ifnull(E.countCon,0) as countCon,ifnull(H.conNum,0) as conNum ", sBuffer.toString(), params.toArray());
	}
	/**
	 * 排序
	 * 
	 * @param pageNum
	 * @param pageSize
	 * @param index
	 * @param goodId
	 * @param storeid
	 * @param sBeginDate
	 * @param sEndDate
	 * @param xBeginDate
	 * @param xEndDate
	 * @param state
	 * @param sellerId
	 * @return
	 */
	public Page<Record> findByPageSortGb(String index, int pageNum, int pageSize, String storeid, int goodId,String sGoodName, String storeid2,
			String sBeginDate, String sEndDate, String xBeginDate, String xEndDate, int state, String sSellerId){
		StringBuffer sBuffer = new StringBuffer("FROM t_bus_goods AS A "
				+ "INNER JOIN t_bus_goods_store_rel F ON A.good_store=F.goodstore "
				+ "INNER JOIN t_dict_store AS B ON F.storeid = B.storeid "
				+ "INNER JOIN t_bus_seller AS C ON B.sellerId = C.sellerId " 
				+ "LEFT JOIN (select goodsid ,count(id) AS countSell from t_bus_order_gb where orderState NOT in(1,5,7,9) group by goodsid) D ON D.goodsid = A.id "
				+ "LEFT JOIN (select goodsid ,sum(amount) AS countPrice from t_bus_order_gb where orderState NOT in(1,5,7,9) group by goodsid) I ON I.goodsid = A.id "
				+ "LEFT JOIN ( SELECT COUNT(id) as countCon,goodsid FROM t_bus_user_collection WHERE isCancel = 0 group by goodsid) E ON E.goodsid = A.id "
				+ "left join t_bus_goods_toker T on T.goodsId=A.id "
				+ " left join t_bus_goods_look H on H.goodsid=A.id where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		sBuffer.append(" AND C.sellerId=? ");
		params.add(sSellerId);
		if(goodId!=0){
			sBuffer.append(" AND A.id=?");
			params.add(goodId);
		}
		 if(!StrKit.isBlank(storeid)){
				sBuffer.append(" AND B.storeid in ('"+storeid+"')");
		 }
		 if(StrKit.notBlank(sGoodName)){
				sBuffer.append(" AND A.name like ?");
				params.add("%"+sGoodName+"%");
			}
		if(!StrKit.isBlank(sBeginDate)&&!StrKit.isBlank(sEndDate)){
			sBuffer.append("AND A.pstate=1 and A.state=2 AND A.uptime BETWEEN ? AND ?");
			params.add(sBeginDate);
			params.add(sEndDate);
		}else if(!StrKit.isBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
			sBuffer.append("AND A.pstate=1 and A.state=2 AND DATE_FORMAT(A.uptime,'%Y-%m-%d')>=?");
			params.add(sBeginDate);
		}else if(StrKit.isBlank(sBeginDate)&&!StrKit.isBlank(sEndDate)){
			sBuffer.append("AND A.pstate=1 and A.state=2 AND DATE_FORMAT(A.uptime,'%Y-%m-%d')<=?");
			params.add(sEndDate);
		}
		if(!StrKit.isBlank(xBeginDate)&&!StrKit.isBlank(xEndDate)){
			sBuffer.append("AND A.pstate=1 and A.state=3 AND A.downtime BETWEEN ? AND ?");
			params.add(xBeginDate);
			params.add(xEndDate);
		}else if(!StrKit.isBlank(xBeginDate)&&StrKit.isBlank(xEndDate)){
			sBuffer.append("AND A.pstate=1 and A.state=3 AND DATE_FORMAT(A.downtime,'%Y-%m-%d')>=?");
			params.add(xBeginDate);
		}else if(StrKit.isBlank(xBeginDate)&&!StrKit.isBlank(xEndDate)){
			sBuffer.append("AND A.pstate=1 and A.state=3 AND DATE_FORMAT(A.downtime,'%Y-%m-%d')<=?");
			params.add(xEndDate);
		}
		switch (state) {
		case 1:
			sBuffer.append(" AND A.state=1");
			break;
		case 2:
			sBuffer.append(" AND A.state=2");
			break;
		case 3:
			sBuffer.append(" AND A.state=3");
			break;
		case 4:
			sBuffer.append(" AND A.state=4");
			break;
		}
		sBuffer.append(" AND A.groupbuying = 2 ");
		//订单量、收藏量、浏览量排序功能，可按大到小排 从小到大
		if(!StrKit.isBlank(index)){
			switch (index) {
				case "1":
					sBuffer.append(" GROUP BY A.id ORDER BY D.countSell DESC");
					break;
				case "2":
					sBuffer.append(" GROUP BY A.id ORDER BY D.countSell ASC");
					break;
				case "3":
					sBuffer.append(" GROUP BY A.id ORDER BY E.countCon DESC");
					break;
				case "4":
					sBuffer.append(" GROUP BY A.id ORDER BY E.countCon ASC");
					break;
				case "5":
					sBuffer.append(" GROUP BY A.id ORDER BY H.conNum DESC");
					break;
				case "6":
					sBuffer.append(" GROUP BY A.id ORDER BY H.conNum ASC");
					break;
				case "7":
					sBuffer.append(" GROUP BY A.id ORDER BY I.countPrice DESC");
					break;
				case "8":
					sBuffer.append(" GROUP BY A.id ORDER BY I.countPrice ASC");
					break;
			}
		}
		return Db.paginate(pageNum, pageSize, "SELECT  A.*,B.title,C.sellerName,C.sellerId,ifnull(D.countSell,0 ) as countSell,ifnull(I.countPrice,0 ) as countPrice,ifnull(E.countCon,0) as countCon,ifnull(H.conNum,0) as conNum,T.state as tState ", sBuffer.toString(), params.toArray());
	}
	/**
	 * 普通商品 日、周、月销售统计
	 * @param pageNum
	 * @param pageSize
	 * @param goodId
	 * @param sellerId
	 * @param storeid
	 * @param sBeginDate
	 * @param sEndDate
	 * @param xBeginDate
	 * @param xEndDate
	 * @param state
	 * @param date
	 * @return
	 */
	public Page<Record> findXSByPage(int pageNum, int pageSize, int goodId, String sellerId, String storeid, String sBeginDate,String sEndDate, String xBeginDate, String xEndDate, int state,int date){
		StringBuffer sBuffer = new StringBuffer("FROM t_bus_goods AS A "
				+ "INNER JOIN t_bus_goods_store_rel F ON A.good_store=F.goodstore "
				+ "INNER JOIN t_dict_store AS B ON F.storeid = B.storeid "
				+ "INNER JOIN t_bus_seller AS C ON B.sellerId = C.sellerId ");
		switch (date) {
		case 1://当日
			sBuffer.append( "LEFT JOIN (select goodsid ,sum(countnum) AS  countSell ,sum(amount) AS countamount from t_bus_order where orderState NOT in(1,5,7,9) and to_days(payDate) =to_days(now()) group by goodsid) D ON D.goodsid = A.id ");
			break;
		case 2://本周
			sBuffer.append( "LEFT JOIN (select goodsid ,sum(countnum) AS countSell ,sum(amount) AS countamount from t_bus_order where orderState NOT in(1,5,7,9) and YEARWEEK(date_format(payDate,'%Y-%m-%d'))= YEARWEEK(now()) group by goodsid) D ON D.goodsid = A.id ");
			break;
		case 3://本月
			sBuffer.append( "LEFT JOIN (select goodsid ,sum(countnum) AS countSell ,sum(amount) AS countamount from t_bus_order where orderState NOT in(1,5,7,9) and date_format(payDate,'%Y-%m')=date_format(now(),'%Y-%m') group by goodsid) D ON D.goodsid = A.id ");
			break;
		default:
			//默认全部
			sBuffer.append( "LEFT JOIN (select goodsid ,sum(countnum) AS countSell ,sum(amount) AS countamount from t_bus_order where orderState NOT in(1,5,7,9)  group by goodsid) D ON D.goodsid = A.id ");
			break;
		}
		sBuffer.append( "LEFT JOIN ( SELECT COUNT(id) as countCon,goodsid FROM t_bus_user_collection WHERE isCancel = 0 group by goodsid) E ON E.goodsid = A.id "
				+ "LEFT JOIN (select J.goodsid ,sum(J.price) AS countPrice from t_bus_order_list J left join t_bus_order K on J.orderNum = K.orderNum  where K.orderState !=1 and K.orderState !=5 group by J.goodsid) I ON I.goodsid = A.id "
				+ "left join t_bus_goods_toker T on T.goodsId=A.id "
				+ " left join t_bus_goods_look H on H.goodsid=A.id where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if(goodId!=0){
			sBuffer.append(" AND A.id=?");
			params.add(goodId);
		}
		if(!StrKit.isBlank(storeid)){
			sBuffer.append(" AND B.storeid in ('"+storeid+"')");
		} 
		if(!StrKit.isBlank(sellerId)){
			sBuffer.append(" AND C.sellerId in ('"+sellerId+"')");
		}
		if(!StrKit.isBlank(sBeginDate)&&!StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND A.uptime BETWEEN ? AND ?");
			params.add(sBeginDate);
			params.add(sEndDate);
		}else if(!StrKit.isBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.uptime,'%Y-%m-%d')>=?");
			params.add(sBeginDate);
		}else if(StrKit.isBlank(sBeginDate)&&!StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.uptime,'%Y-%m-%d')<=?");
			params.add(sEndDate);
		}
		if(!StrKit.isBlank(xBeginDate)&&!StrKit.isBlank(xEndDate)){
			sBuffer.append(" AND A.downtime BETWEEN ? AND ?");
			params.add(xBeginDate);
			params.add(xEndDate);
		}else if(!StrKit.isBlank(xBeginDate)&&StrKit.isBlank(xEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.downtime,'%Y-%m-%d')>=?");
			params.add(xBeginDate);
		}else if(StrKit.isBlank(xBeginDate)&&!StrKit.isBlank(xEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.downtime,'%Y-%m-%d')<=?");
			params.add(xEndDate);
		}
		switch (state) {
		case 1:
			sBuffer.append(" AND A.state=1");
			break;
		case 2:
			sBuffer.append(" AND A.state=2");
			break;
		case 3:
			sBuffer.append(" AND A.state=3");
			break;
		case 4:
			sBuffer.append(" AND A.state=4");
			break;
		}
		sBuffer.append(" AND A.groupbuying = 1 ");
		sBuffer.append(" ORDER BY  A.id DESC");
		return Db.paginate(pageNum, pageSize, "SELECT  A.*,B.title,C.sellerName,C.sellerId,ifnull(D.countSell,0 ) as countSell,ifnull(I.countPrice,0 ) as countPrice,ifnull(E.countCon,0) as countCon,ifnull(H.conNum,0) as conNum,T.state as tState ", sBuffer.toString(), params.toArray());
	}
	/**
	 * 团购商品 日、周、月销售统计
	 * @param pageNum
	 * @param pageSize
	 * @param goodId
	 * @param sellerId
	 * @param storeid
	 * @param sBeginDate
	 * @param sEndDate
	 * @param xBeginDate
	 * @param xEndDate
	 * @param state
	 * @param date
	 * @return
	 */
	public Page<Record> findXSByPagegb(int pageNum, int pageSize, int goodId, String sellerId, String storeid, String sBeginDate,String sEndDate, String xBeginDate, String xEndDate, int state,int date){
		StringBuffer sBuffer = new StringBuffer("FROM t_bus_goods AS A "
				+ "INNER JOIN t_bus_goods_store_rel F ON A.good_store=F.goodstore "
				+ "INNER JOIN t_dict_store AS B ON F.storeid = B.storeid "
				+ "inner JOIN t_bus_seller AS C ON B.sellerId= C.sellerId ");
		switch (date) {
		case 1://当日
			sBuffer.append( "LEFT JOIN (select goodsid ,sum(countnum) AS countSell from t_bus_order_gb where orderState NOT in(1,5,7,9) and to_days(payDate) =to_days(now()) group by goodsid) D ON D.goodsid = A.id ");
			break;
		case 2://本周
			sBuffer.append( "LEFT JOIN (select goodsid ,sum(countnum) AS countSell from t_bus_order_gb where orderState NOT in(1,5,7,9) and YEARWEEK(date_format(payDate,'%Y-%m-%d'))= YEARWEEK(now()) group by goodsid) D ON D.goodsid = A.id ");
			break;
		case 3://本月
			sBuffer.append( "LEFT JOIN (select goodsid ,sum(countnum) AS countSell from t_bus_order_gb where orderState NOT in(1,5,7,9) and date_format(payDate,'%Y-%m')=date_format(now(),'%Y-%m') group by goodsid) D ON D.goodsid = A.id ");
			break;
		default:
			//默认全部
			sBuffer.append( "LEFT JOIN (select goodsid ,sum(countnum) AS countSell from t_bus_order_gb where orderState NOT in(1,5,7,9) group by goodsid) D ON D.goodsid = A.id ");
			break;
		}
		sBuffer.append( "LEFT JOIN ( SELECT COUNT(id) as countCon,goodsid FROM t_bus_user_collection WHERE isCancel = 0 group by goodsid) E ON E.goodsid = A.id "
				+ "LEFT JOIN (select goodsid ,sum(amount) AS countPrice from t_bus_order_gb where orderState NOT in(1,5,7,9) group by goodsid) I ON I.goodsid = A.id "
				+ "left join t_bus_goods_toker T on T.goodsId=A.id "
				+ " left join t_bus_goods_look H on H.goodsid=A.id where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if(goodId!=0){
			sBuffer.append(" AND A.id=?");
			params.add(goodId);
		}
		if(!StrKit.isBlank(storeid)){
			sBuffer.append(" AND B.storeid in ('"+storeid+"')"+" AND C.sellerId in ('"+sellerId+"')");
		}else if(!StrKit.isBlank(sellerId)){
			sBuffer.append(" AND C.sellerId in ('"+sellerId+"')");
		}
		if(!StrKit.isBlank(sBeginDate)&&!StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND A.uptime BETWEEN ? AND ? and A.state=2 and pstate=1");
			params.add(sBeginDate);
			params.add(sEndDate);
		}else if(!StrKit.isBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.uptime,'%Y-%m-%d')>=? and A.state=2 and pstate=1");
			params.add(sBeginDate);
		}else if(StrKit.isBlank(sBeginDate)&&!StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.uptime,'%Y-%m-%d')<=? and A.state=2 and pstate=1");
			params.add(sEndDate);
		}
		if(!StrKit.isBlank(xBeginDate)&&!StrKit.isBlank(xEndDate)){
			sBuffer.append(" AND A.downtime BETWEEN ? AND ? and A.state=3 and pstate=1");
			params.add(xBeginDate);
			params.add(xEndDate);
		}else if(!StrKit.isBlank(xBeginDate)&&StrKit.isBlank(xEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.downtime,'%Y-%m-%d')>=? and A.state=3 and pstate=1");
			params.add(xBeginDate);
		}else if(StrKit.isBlank(xBeginDate)&&!StrKit.isBlank(xEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.downtime,'%Y-%m-%d')<=? and A.state=3 and pstate=1");
			params.add(xEndDate);
		}
		switch (state) {
		case 1:
			sBuffer.append(" AND A.state=1");
			break;
		case 2:
			sBuffer.append(" AND A.state=2");
			break;
		case 3:
			sBuffer.append(" AND A.state=3");
			break;
		case 4:
			sBuffer.append(" AND A.state=4");
			break;
		}
		sBuffer.append(" AND A.groupbuying = 2 ");
		sBuffer.append(" GROUP BY A.id ORDER BY countSell desc, A.id DESC");
		return Db.paginate(pageNum, pageSize, "SELECT  A.*,B.title,C.sellerName,C.sellerId,ifnull(D.countSell,0 ) as countSell,ifnull(I.countPrice,0 ) as countPrice,ifnull(E.countCon,0) as countCon,ifnull(H.conNum,0) as conNum,T.state as tState ", sBuffer.toString(), params.toArray());
	}
	/**
	 * 查找分公司产品
	 */
	public GoodsStore findById(int id){
		return GoodsStore.me().findById(id);
	}
	/**
	 *增加便利店商品
	 */
	public boolean add(GoodsStore goods){
		return goods.save();
	}
	/**
	 *修改便利店商品
	 */
	public boolean update(GoodsStore goods){
		return goods.update();
	}
	/**
	 *查找出当前商品的订单信息
	 */
	public Page<Record> findOrderById(int pageNum,int pageSize,int id,int state,String sSellerId,String storeid){
		StringBuffer sBuffer = new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		sBuffer.append(" FROM t_bus_order_list LI "
				+ "LEFT JOIN t_bus_order A ON LI.orderNum = A.orderNum "
				+ "LEFT JOIN t_bus_goods AS G ON  G.id=LI.goodsid "
				+ "LEFT JOIN t_bus_goods_store_rel H on H.goodstore = G.good_store "
				+ "LEFT JOIN t_dict_store B ON H.storeid = B.storeid "
				+ "LEFT JOIN t_bus_seller C ON B.sellerId=C.sellerId "
				+ "LEFT JOIN t_bus_order_toker_user_log L ON A.orderNum=L.orderNum "
				+ "LEFT JOIN t_bus_user F ON A.userid=F.id "
				+ " WHERE 1=1 ");
		if(id>0){
		sBuffer.append(" AND G.id=? ");	
		params.add(id);	
		}
		if (StrKit.notBlank(sSellerId)) {
			sBuffer.append(" AND C.sellerId=? ");	
			params.add(sSellerId);	
		}
		if (StrKit.notBlank(storeid)) {
			sBuffer.append(" AND B.storeid=? ");	
			params.add(storeid);	
		}
		
		 switch (state) {
			case 1:
				sBuffer.append(" AND A.orderState=?");
				params.add(state);
				break;
			case 2:
				sBuffer.append(" AND A.orderState=?");
				params.add(state);
				break;
			case 3:
				sBuffer.append(" AND A.orderState=?");
				params.add(state);
				break;
			case 4:
				sBuffer.append(" AND A.orderState=?");
				params.add(state);
				break;
			case 5:
				sBuffer.append(" AND A.orderState=?");
				params.add(state);
				break;
			case 6:
				sBuffer.append(" AND A.orderState=?");
				params.add(state);
				break;
			case 7:
				sBuffer.append(" AND A.orderState=?");
				params.add(state);
				break;
			case 9:
				sBuffer.append(" AND A.orderState=?");
				params.add(state);
				break;
			
			default:
				//默认不查删除的订单,退款,过期
				sBuffer.append(" AND A.orderState!=5");
				sBuffer.append(" AND A.orderState!=1");
				sBuffer.append(" AND A.orderState!=7");
				sBuffer.append(" AND A.orderState!=9");
				break;
			}
		sBuffer.append(" AND G.groupbuying = 1 ");
		sBuffer.append(" ORDER BY A.createDate DESC");
		
		Page<Record> pagee = Db.paginate(pageNum,pageSize,"SELECT A.*,F.id as uid,F.name,F.card,B.title as xdstore  ",sBuffer.toString(),params.toArray());
		return pagee;
	}
	/**
	 *查找出团购订单信息
	 */
	public Page<Record> findOrderByIdgb(int pageNum,int pageSize,int id,int state,String sSellerId,String storeid){
		StringBuffer sBuffer = new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		sBuffer.append(" FROM t_bus_order_gb A "
				+ "left JOIN t_bus_goods AS G ON  G.id=A.goodsid "
				+ "LEFT JOIN t_bus_goods_store_rel H on H.goodstore = G.good_store "
				+ "LEFT JOIN t_dict_store B ON H.storeid = B.storeid "
				+ "LEFT JOIN t_bus_seller C ON B.sellerId=C.sellerId "
				+ "LEFT JOIN t_bus_order_toker_user_log L ON A.orderNum=L.orderNum "
				+ "LEFT JOIN t_bus_user F ON A.userid=F.id "
				+ "LEFT JOIN t_dict_store E ON A.dhstoreid = E.storeid WHERE 1=1 ");
		if(id>0){
		sBuffer.append(" AND G.id=?");	
		params.add(id);	
		}
		if (StrKit.notBlank(sSellerId)) {
			sBuffer.append(" AND C.sellerId=? ");	
			params.add(sSellerId);	
		}
		if (StrKit.notBlank(storeid)) {
			sBuffer.append(" AND B.storeid=? ");	
			params.add(storeid);	
		}
		 switch (state) {
			case 1:
				sBuffer.append(" AND A.orderState=?");
				params.add(state);
				break;
			case 2:
				sBuffer.append(" AND A.orderState=?");
				params.add(state);
				break;
			case 3:
				sBuffer.append(" AND A.orderState=?");
				params.add(state);
				break;
			case 4:
				sBuffer.append(" AND A.orderState=?");
				params.add(state);
				break;
			case 5:
				sBuffer.append(" AND A.orderState=?");
				params.add(state);
				break;
			case 6:
				sBuffer.append(" AND A.orderState=?");
				params.add(state);
				break;
			case 7:
				sBuffer.append(" AND A.orderState=?");
				params.add(state);
				break;
			case 9:
				sBuffer.append(" AND A.orderState=?");
				params.add(state);
				break;
			
			default:
				//默认不查删除的订单,退款,过期
				sBuffer.append(" AND A.orderState!=5");
				sBuffer.append(" AND A.orderState!=1");
				sBuffer.append(" AND A.orderState!=7");
				sBuffer.append(" AND A.orderState!=9");
				break;
			}
		sBuffer.append(" AND G.groupbuying = 2 ");
		sBuffer.append(" ORDER BY A.createDate DESC");
		Page<Record> pagee = Db.paginate(pageNum,pageSize,"SELECT A.*,F.id as uid,F.name,F.card,B.title as xdstore ",sBuffer.toString(),params.toArray());
		return pagee;
	}
	
	/**
	 * 获取公司的sellerId
	 * @param userid
	 * @return
	 */	
	public Seller findByUserId(int userid){
		return Seller.me().findFirst("select sellerId from t_bus_seller where userid=?",userid);
	}

	/**
	 * 图片排序
	 * @param sortMap
	 * @return
	 *//*
	public boolean sortImages(Map<String,String> sortMap){
		int batchSize = sortMap.size();
		List<String> sqlList = new ArrayList<String>();
		for(String id : sortMap.keySet()){
			sqlList.add("UPDATE t_bus_goods_images SET orderNo="+sortMap.get(id)+" WHERE id="+id);
		}
		int[] result = Db.batch(sqlList,batchSize);
		return result.length>0;
	}
	*/
	/**
	 * 商品图片列表
	 * @param goodsid
	 * @return
	 */
	public List<GoodsBaseImages> findAllImages(String LunBoNum){
		return GoodsBaseImages.me().find("SELECT A.* FROM t_bus_goods_base_images A WHERE A.LunBoNum=? ORDER BY A.orderNo",LunBoNum);
	}
	/**
	 * 商品详情图片列表
	 * 
	 * @return
	 */
	public List<DetailsImg> findDetailImages(String LunBoNum){
		return DetailsImg.me().find("SELECT A.* FROM goods_details_images A WHERE A.LunBoNum=? ORDER BY A.orderNo",LunBoNum);
	}
	
	/**
	 * 设置图片为商品logo
	 * @param id
	 * @return
	 */
	@Before(Tx.class)
	public boolean setImageLogo(int goodsid,int id){
		List<String> sqlList = new ArrayList<String>();
		//更新该商品所有LOGO的isLogo标识为0
		sqlList.add("UPDATE t_bus_goods_images SET isLogo=0 WHERE goodsid="+goodsid);
		//更新当前LOGO的isLogo标识为1
		sqlList.add("UPDATE t_bus_goods_images SET isLogo=1 WHERE goodsid="+goodsid+" AND id="+id);
		//更新该商品的LOGO字段值为当前LOGO的地址
		sqlList.add("UPDATE t_bus_goods SET thumb=(SELECT thumb FROM t_bus_goods_images WHERE id="+id+") WHERE id="+goodsid);
		Db.batch(sqlList, sqlList.size());
		return true;
	}

	//商品统计信息
	public Record countNumber(String sellerId,int RoleId) {
		if(RoleId==1||RoleId==2){
			return Db.findFirst("select A.tolnumber,B.sjshnumber,C.sjnumber,D.xjnumber FROM "
					+ "(select COUNT(DISTINCT AA.id) as tolnumber FROM t_bus_goods AS AA INNER JOIN t_bus_goods_store_rel DD on AA.good_store=DD.goodstore INNER JOIN t_dict_store AS BB ON DD.storeid = BB.storeid inner JOIN t_bus_seller AS CC ON BB.sellerId= CC.sellerId  where AA.groupbuying=1 and BB.sellerId=?) A,"
					+ "(select COUNT(DISTINCT AA.id) as sjshnumber  FROM t_bus_goods AS AA INNER JOIN t_bus_goods_store_rel DD on AA.good_store=DD.goodstore INNER JOIN t_dict_store AS BB ON DD.storeid = BB.storeid inner JOIN t_bus_seller AS CC ON BB.sellerId= CC.sellerId where AA.groupbuying=1 and AA.state=1 and AA.pstate=1 and BB.sellerId=?) B,"
					+ "(select COUNT(DISTINCT AA.id) as sjnumber FROM t_bus_goods AS AA INNER JOIN t_bus_goods_store_rel DD on AA.good_store=DD.goodstore INNER JOIN t_dict_store AS BB ON DD.storeid = BB.storeid inner JOIN t_bus_seller AS CC ON BB.sellerId= CC.sellerId where AA.groupbuying=1 and AA.state=2 and AA.pstate=1 and BB.sellerId=?) C,"
					+ "(select COUNT(DISTINCT AA.id) as xjnumber FROM t_bus_goods AS AA INNER JOIN t_bus_goods_store_rel DD on AA.good_store=DD.goodstore INNER JOIN t_dict_store AS BB ON DD.storeid = BB.storeid inner JOIN t_bus_seller AS CC ON BB.sellerId= CC.sellerId where AA.groupbuying=1 and AA.state=3 and AA.pstate=1 and BB.sellerId=?) D",
					
					sellerId,sellerId,sellerId,sellerId);
		}else{
			return Db.findFirst("select A.tolnumber,B.sjshnumber,C.sjnumber,D.xjnumber FROM "
					+ "(select COUNT(DISTINCT AA.id) as tolnumber FROM t_bus_goods AS AA INNER JOIN t_bus_goods_store_rel DD on AA.good_store=DD.goodstore INNER JOIN t_dict_store AS BB ON DD.storeid = BB.storeid inner JOIN t_bus_seller AS CC ON BB.sellerId= CC.sellerId  where AA.groupbuying=1 and BB.storeid=?) A,"
					+ "(select COUNT(DISTINCT AA.id) as sjshnumber FROM t_bus_goods AS AA INNER JOIN t_bus_goods_store_rel DD on AA.good_store=DD.goodstore INNER JOIN t_dict_store AS BB ON DD.storeid = BB.storeid inner JOIN t_bus_seller AS CC ON BB.sellerId= CC.sellerId where AA.groupbuying=1 and AA.state=1 and AA.pstate=1 and BB.storeid=?) B,"
					+ "(select COUNT(DISTINCT AA.id) as sjnumber FROM t_bus_goods AS AA INNER JOIN t_bus_goods_store_rel DD on AA.good_store=DD.goodstore INNER JOIN t_dict_store AS BB ON DD.storeid = BB.storeid inner JOIN t_bus_seller AS CC ON BB.sellerId= CC.sellerId where AA.groupbuying=1 and AA.state=2 and AA.pstate=1 and BB.storeid=?) C,"
					+ "(select COUNT(DISTINCT AA.id) as xjnumber FROM t_bus_goods AS AA INNER JOIN t_bus_goods_store_rel DD on AA.good_store=DD.goodstore INNER JOIN t_dict_store AS BB ON DD.storeid = BB.storeid inner JOIN t_bus_seller AS CC ON BB.sellerId= CC.sellerId where AA.groupbuying=1 and AA.state=3 and AA.pstate=1 and BB.storeid=?) D",
					sellerId,sellerId,sellerId,sellerId);
		}
	}
	//团购商品统计信息
	public Record countNumbergb(String sellerId,int RoleId) {
		if(RoleId==1||RoleId==2){
			return Db.findFirst("select A.tolnumber,B.sjshnumber,C.sjnumber,D.xjnumber,E.gbnumber FROM "
					+ "(select COUNT(DISTINCT AA.id) as gbnumber FROM t_bus_goods AS AA INNER JOIN t_bus_goods_store_rel DD on AA.good_store=DD.goodstore INNER JOIN t_dict_store AS BB ON DD.storeid = BB.storeid inner JOIN t_bus_seller AS CC ON BB.sellerId= CC.sellerId  where AA.groupbuying=2 and BB.sellerId=?) E,"
					+ "(select COUNT(DISTINCT AA.id) as tolnumber FROM t_bus_goods AS AA INNER JOIN t_bus_goods_store_rel DD on AA.good_store=DD.goodstore INNER JOIN t_dict_store AS BB ON DD.storeid = BB.storeid inner JOIN t_bus_seller AS CC ON BB.sellerId= CC.sellerId  where AA.groupbuying=2 and BB.sellerId=?) A,"
					+ "(select COUNT(DISTINCT AA.id) as sjshnumber  FROM t_bus_goods AS AA INNER JOIN t_bus_goods_store_rel DD on AA.good_store=DD.goodstore INNER JOIN t_dict_store AS BB ON DD.storeid = BB.storeid inner JOIN t_bus_seller AS CC ON BB.sellerId= CC.sellerId where AA.groupbuying=2 and AA.state=1 and AA.pstate=1 and BB.sellerId=?) B,"
					+ "(select COUNT(DISTINCT AA.id) as sjnumber FROM t_bus_goods AS AA INNER JOIN t_bus_goods_store_rel DD on AA.good_store=DD.goodstore INNER JOIN t_dict_store AS BB ON DD.storeid = BB.storeid inner JOIN t_bus_seller AS CC ON BB.sellerId= CC.sellerId where AA.groupbuying=2 and AA.state=2 and AA.pstate=1 and BB.sellerId=?) C,"
					+ "(select COUNT(DISTINCT AA.id) as xjnumber FROM t_bus_goods AS AA INNER JOIN t_bus_goods_store_rel DD on AA.good_store=DD.goodstore INNER JOIN t_dict_store AS BB ON DD.storeid = BB.storeid inner JOIN t_bus_seller AS CC ON BB.sellerId= CC.sellerId where AA.groupbuying=2 and AA.state=3 and AA.pstate=1 and BB.sellerId=?) D",
					
					sellerId,sellerId,sellerId,sellerId,sellerId);
		}else{
			return Db.findFirst("select A.tolnumber,B.sjshnumber,C.sjnumber,D.xjnumber,E.gbnumber FROM "
					+ "(select COUNT(DISTINCT AA.id) as gbnumber FROM t_bus_goods AS AA INNER JOIN t_bus_goods_store_rel DD on AA.good_store=DD.goodstore INNER JOIN t_dict_store AS BB ON DD.storeid = BB.storeid inner JOIN t_bus_seller AS CC ON BB.sellerId= CC.sellerId  where AA.groupbuying=2 and BB.storeid=?) E,"
					+ "(select COUNT(DISTINCT AA.id) as tolnumber FROM t_bus_goods AS AA INNER JOIN t_bus_goods_store_rel DD on AA.good_store=DD.goodstore INNER JOIN t_dict_store AS BB ON DD.storeid = BB.storeid inner JOIN t_bus_seller AS CC ON BB.sellerId= CC.sellerId  where AA.groupbuying=2 and BB.storeid=?) A,"
					+ "(select COUNT(DISTINCT AA.id) as sjshnumber FROM t_bus_goods AS AA INNER JOIN t_bus_goods_store_rel DD on AA.good_store=DD.goodstore INNER JOIN t_dict_store AS BB ON DD.storeid = BB.storeid inner JOIN t_bus_seller AS CC ON BB.sellerId= CC.sellerId where AA.groupbuying=2 and AA.state=1 and AA.pstate=1 and BB.storeid=?) B,"
					+ "(select COUNT(DISTINCT AA.id) as sjnumber FROM t_bus_goods AS AA INNER JOIN t_bus_goods_store_rel DD on AA.good_store=DD.goodstore INNER JOIN t_dict_store AS BB ON DD.storeid = BB.storeid inner JOIN t_bus_seller AS CC ON BB.sellerId= CC.sellerId where AA.groupbuying=2 and AA.state=2 and AA.pstate=1 and BB.storeid=?) C,"
					+ "(select COUNT(DISTINCT AA.id) as xjnumber FROM t_bus_goods AS AA INNER JOIN t_bus_goods_store_rel DD on AA.good_store=DD.goodstore INNER JOIN t_dict_store AS BB ON DD.storeid = BB.storeid inner JOIN t_bus_seller AS CC ON BB.sellerId= CC.sellerId where AA.groupbuying=2 and AA.state=3 and AA.pstate=1 and BB.storeid=?) D",
					sellerId,sellerId,sellerId,sellerId,sellerId);
		}
	}
	public boolean tooDown(GoodsStore goods) {
		return goods.update();
	}
	public Record findGoodDetailById(int id) {
		return Db.findFirst("select * from t_bus_goods where id=?",id);
	}
	
	public boolean edit(GoodsStore goods) {
		return goods.update();
	}
	public List<Record> getGoodsItem(String gooditem) {
		return Db.find("select * from t_bus_goods_item where goodItem=?",gooditem);
	}
	public List<Record> getStore(String good_store) {
		return Db.find("SELECT A.title,A.address,A.tel FROM t_dict_store A WHERE A.storeid IN (select storeid from t_bus_goods_store_rel where goodstore=?) and A.qstate=1 and A.state=2 ",good_store);
	}
	
	/**
	 * 商品规格
	 * @param gooditem
	 * @return
	 */
	/*public List<Record> getGoodSpec(int goodsId) {
		return Db.find("select * from t_bus_goods where id=?",goodsId);
	}*/
	public List<Record> getGoodSpec(int goodsId) {
		return Db.find("select * from t_bus_goods_spec where goodId=?",goodsId);
	}
	
	/**
	 * 导出数据
	 * @param response
	 * @param request
	 * @param listz 
	 * @param titles2 
	 * @param member
	 */
	public void export(HttpServletResponse response, HttpServletRequest request, List<Pair> titles, List<Record> listz) {
		ExcelExportUtil.exportByRecord(response, request, "团购商品统计列表", titles, listz);
	}
	public List<Record> findExcelByPage(int goodId, String sellerId, String storeid, String sBeginDate, String sEndDate,
			String xBeginDate, String xEndDate, int state, int date) {
		StringBuffer sBuffer = new StringBuffer("SELECT A.*,GROUP_CONCAT(B.title) as title,C.sellerName,C.sellerId,ifnull(D.countSell,0 ) as countSell,"
				+ "ifnull(E.countCon,0) as countCon,ifnull(H.conNum,0) as conNum,T.state as tState "
				+ "FROM t_bus_goods AS A INNER JOIN t_bus_goods_store_rel F ON A.good_store=F.goodstore INNER JOIN t_dict_store AS B ON F.storeid = B.storeid inner JOIN t_bus_seller AS C ON B.sellerId= C.sellerId left join t_bus_goods_toker T on T.goodsId=A.id ");
		switch (date) {
		case 1://当日
			sBuffer.append( "LEFT JOIN (select goodsid ,sum(countnum) AS  countSell ,sum(amount) AS countamount from t_bus_order where orderState NOT in(1,5,7,9) and to_days(payDate) =to_days(now()) group by goodsid) D ON D.goodsid = A.id ");
			break;
		case 2://本周
			sBuffer.append( "LEFT JOIN (select goodsid ,sum(countnum) AS countSell ,sum(amount) AS countamount from t_bus_order where orderState NOT in(1,5,7,9) and YEARWEEK(date_format(payDate,'%Y-%m-%d'))= YEARWEEK(now()) group by goodsid) D ON D.goodsid = A.id ");
			break;
		case 3://本月
			sBuffer.append( "LEFT JOIN (select goodsid ,sum(countnum) AS countSell ,sum(amount) AS countamount from t_bus_order where orderState NOT in(1,5,7,9) and date_format(payDate,'%Y-%m')=date_format(now(),'%Y-%m') group by goodsid) D ON D.goodsid = A.id ");
			break;
		default:
			//默认全部
			sBuffer.append( "LEFT JOIN (select goodsid ,sum(countnum) AS countSell ,sum(amount) AS countamount from t_bus_order where orderState NOT in(1,5,7,9)  group by goodsid) D ON D.goodsid = A.id ");
			break;
		}
		sBuffer.append( "LEFT JOIN ( SELECT COUNT(id) as countCon,goodsid FROM t_bus_user_collection WHERE isCancel = 0 group by goodsid) E ON E.goodsid = A.id"
				+ " left join t_bus_goods_look H on H.goodsid=A.id where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		sBuffer.append(" AND C.sellerId = ?");
		params.add(sellerId);
		if(goodId!=0){
			sBuffer.append(" AND A.id=?");
			params.add(goodId);
		}
		if(!StrKit.isBlank(storeid)){
			sBuffer.append(" AND B.storeid in ('"+storeid+"')"+" AND C.sellerId in ('"+sellerId+"')");
			//sBuffer.append(" AND B.storeid in ('"+storeid+"')"+" AND C.sellerId in ('"+sellerId+"')");
		}
		if(!StrKit.isBlank(sBeginDate)&&!StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND A.uptime BETWEEN ? AND ? and A.state=2 and pstate=1");
			params.add(sBeginDate);
			params.add(sEndDate);
		}else if(!StrKit.isBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.uptime,'%Y-%m-%d')>=? and A.state=2 and pstate=1");
			params.add(sBeginDate);
		}else if(StrKit.isBlank(sBeginDate)&&!StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.uptime,'%Y-%m-%d')<=? and A.state=2 and pstate=1");
			params.add(sEndDate);
		}
		if(!StrKit.isBlank(xBeginDate)&&!StrKit.isBlank(xEndDate)){
			sBuffer.append(" AND A.downtime BETWEEN ? AND ? and A.state=3 and pstate=1");
			params.add(xBeginDate);
			params.add(xEndDate);
		}else if(!StrKit.isBlank(xBeginDate)&&StrKit.isBlank(xEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.downtime,'%Y-%m-%d')>=? and A.state=3 and pstate=1");
			params.add(xBeginDate);
		}else if(StrKit.isBlank(xBeginDate)&&!StrKit.isBlank(xEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.downtime,'%Y-%m-%d')<=? and A.state=3 and pstate=1");
			params.add(xEndDate);
		}
		switch (state) {
		case 1:
			sBuffer.append(" AND A.state=1");
			break;
		case 2:
			sBuffer.append(" AND A.state=2");
			break;
		case 3:
			sBuffer.append(" AND A.state=3");
			break;
		case 4:
			sBuffer.append(" AND A.state=4");
			break;
		}
		// GROUP BY A.id ORDER BY A.createDate DESC
		sBuffer.append(" group by A.id");
		sBuffer.append(" ORDER BY  A.id DESC");
		return Db.find( sBuffer.toString(), params.toArray());
	}

    /**
     * 根据商品id查询商品名称和积分
     * @param id
     * @return
     */
	public Record findGoodsById(int id) {
		return Db.findFirst("select g.name from t_bus_goods g left join t_bus_goods_sale s on s.goodsid=g.id  where g.id=?",id);
	}
    /***
     * 根据商品id查询收藏该商品的用户
     * @param pageNum
     * @param pageSize
     * @param id
     * @return
     */
	public Page<Record> findGoodsByCountCon( int pageNum, int pageSize,int id) {
		String sql="from t_bus_user_collection C left join  t_bus_user_wx  WX on WX.id=C.userid left join  t_bus_user U on U.id=WX.userid where C.goodsid="+id+" ";
		return Db.paginate(pageNum, pageSize,
				"select U.*,(select count(*)  from t_bus_user_collection C left join t_bus_order BD on BD.goodsid=C.goodsid where C.goodsid="+id+" AND BD.payState=2 ) csptnum,"
						+ " (select count(*)  from t_bus_user_collection C left join t_bus_order BD on BD.goodsid=C.goodsid where C.goodsid="+id+" AND BD.orderState=2) excnum , "
						+ "  (select sum(amount)  from t_bus_user_collection C left join t_bus_order BD on BD.goodsid=C.goodsid where C.goodsid="+id+" AND BD.payState=2  ) amount ",
				sql);
	}
	 /***
	  * 数据导出
     * 根据商品id查询收藏该商品的用户
     * @param pageNum
     * @param pageSize
     * @param id
     * @return
     */
	public List<Record> findGoodsByCountConExcel(int id) {
		String sql="select U.*,(select count(*)  from t_bus_user_collection C left join t_bus_order BD on BD.goodsid=C.goodsid where C.goodsid="+id+" AND BD.payState=2 ) csptnum,"
				+ " (select count(*)  from t_bus_user_collection C left join t_bus_order BD on BD.goodsid=C.goodsid where C.goodsid="+id+" AND BD.orderState=2) excnum , "
				+ "  (select sum(amount)  from t_bus_user_collection C left join t_bus_order BD on BD.goodsid=C.goodsid where C.goodsid="+id+" AND BD.payState=2  ) amount "
				+" from t_bus_user_collection C left join  t_bus_user_wx  WX on WX.id=C.userid left join  t_bus_user U on U.id=WX.userid where C.goodsid="+id+" ";
		return Db.find(sql);
	}
	/**
	 * 根据商品id查询所有的SKU
	 * @param id
	 * @return
	 */
	public List<GoodsSale> findAllSale(int goodsid){
		return GoodsSale.me().find("SELECT * from  t_bus_goods_sale  where goodsid=?",goodsid);
	}
	/**
	 * 查询所有的一级category
	 * @param iid
	 * @return
	 */
	public List<Category> finOneCategory(){
		return Category.me().find("SELECT * FROM  t_dict_category  WHERE categoryType=1");
	}
	/**
	 * 查询所有的二级category
	 * @param iid
	 * @return
	 */
	public List<Category> findTwoCategory(){
		return Category.me().find("SELECT * FROM  t_dict_category  WHERE categoryType=2");
	}
	/**
	 * 查询所有的三级category
	 * @param iid
	 * @return
	 */
	public List<Category> findThreeCategory(){
		return Category.me().find("SELECT * FROM  t_dict_category  WHERE categoryType=3");
	}
	/**
	 * 查询所有的四级category
	 * @param iid
	 * @return
	 */
	public List<Category> findFourCategory(){
		return Category.me().find("SELECT * FROM  t_dict_category  WHERE categoryType=4");
	}

	/**
	 * 查询所有的扩展信息
	 * @return
	 */
	public List<GoodsSaleItem> findSaleItem(int goodsid){
		return GoodsSaleItem.me().find("SELECT * FROM  t_bus_goods_sale_items where goodsid =?",goodsid);
	}
	/**
	 * 根据商品id查询当前商品的拓客信息
	 * @param id
	 * @return
	 */
	public Record findtoker(int id) {
		return Db.findFirst("select * from t_bus_goods_toker where goodsId=?",id);
	}
	
}